﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient
Public Class NhaDAO

    Public Shared Function GetAll() As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectNha"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        Return listNha
    End Function

    Public Shared Function GetAllNhaTamXoa() As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectNhaTamXoa"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNha
    End Function

    Public Shared Function ExportDS_Nha() As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "exportDS_Nha"
        cmd.Connection = cn

        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        da.Fill(listNha)

        Return listNha
    End Function

    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertNha"
            cmd.Connection = cn
            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = ""    'Bo sung ten store
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateNha"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub
    Public Shared Sub Update_TamXoa(ByVal parameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateNhaTamXoa"
            cmd.Connection = cn

            cmd.Parameters.AddRange(parameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Function LayMa_Nha_Cuoi() As String
        Dim macuoi As String = ""

        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayMa_Nha_Cuoi"
            cmd.Connection = cn

            cn.Open()
            macuoi = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return macuoi
    End Function

    Public Shared Function LayDSMaNha() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayDSMaNha"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

            da.Fill(dt)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return dt
    End Function
    Public Shared Function LaySLNhaTaiQuan(ByVal paraMaQuan As SqlParameter) As Integer
        Dim iSLNha As Integer = 0
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LaySLNhaTaiQuan"
            cmd.Parameters.Add(paraMaQuan)
            cmd.Connection = cn

            cn.Open()
            iSLNha = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return iSLNha
    End Function

    Public Shared Function TimKiemNhaTheoQuan(ByVal paraMaQuan As SqlParameter) As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemNhaTheoQuan"
            cmd.Connection = cn
            cmd.Parameters.Add(paraMaQuan)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNha
    End Function

    Public Shared Function TimKiemNhaTheoLoaiNha(ByVal paraMaLoaiNha As SqlParameter) As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemNhaTheoLoaiNha"
            cmd.Connection = cn
            cmd.Parameters.Add(paraMaLoaiNha)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNha
    End Function

    Public Shared Function TimKiemNhaTheoTinhTrang(ByVal paraTinhtrang As SqlParameter) As DataTable
        Dim listNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemNhaTheoLoaiTinhTrang"
            cmd.Connection = cn
            cmd.Parameters.Add(paraTinhtrang)

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(listNha)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNha
    End Function
End Class
